"""
Case Type   : 事件触发器
Case Name   : 【事件触发器】CREATE/ALTER/DROP AGGREGATE命令触发事件触发器
Create At   : 2024/07/08
Owner       : sungang14
Description :
    预置条件：
        1.创建事件触发器：
        CREATE OR REPLACE FUNCTION f_eventtrigger_0016()
        RETURNS event_trigger
        LANGUAGE plpgsql
        AS $$ BEGIN
        RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
        END;
        $$;
        CREATE EVENT AGGREGATE start_eventtri ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0029();
        CREATE EVENT AGGREGATE end_eventtri ON ddl_command_end EXECUTE PROCEDURE f_eventtrigger_0029();
        CREATE EVENT AGGREGATE drop_eventtri ON sql_drop EXECUTE PROCEDURE f_eventtrigger_0029();
        CREATE EVENT AGGREGATE rewrite_eventtri ON  table_rewrite EXECUTE PROCEDURE f_eventtrigger_0029(); 
       2、创建聚合函数：
        create type type_eventtrigger_0029 as (total bigint, count bigint);
        create or replace function f__eventtrigger_0029_01(state type_eventtrigger_0029, n int) return type_eventtrigger_0029 as
        declare new_state type_eventtrigger_0029;
        begin
        raise notice 'f_eventtrigger_0029_01 called with %', n;
        if state is null then
        if n is not null then
        new_state.total := n;
        new_state.count := 1;
        return new_state;
        end if;
        return null;
        elsif n is not null then
        state.total := state.total + n;
        state.count := state.count + 1;
        return state;
        end if;
        return null;
        end;
        /
        3、
        create or replace function f_eventtrigger_0029_02(state type_eventtrigger_0029) return int4 as
        begin
        if state is null then
        return NULL;
        else
        return state.total / state.count;
        end if;
        end;
        /
    测试步骤：
        1、create aggregate a__eventtrigger_0029(int4)
        (
            stype = type_eventtrigger_0029,
            sfunc = f__eventtrigger_0029_01,
            finalfunc = f__eventtrigger_0029_02
        );
        2、ALTER AGGREGATE a__eventtrigger_0029(int4) RENAME TO a__eventtrigger_00292;
        3、drop aggregate a__eventtrigger_00292(int4);
Expect      : 
    1、触发执行函数f_eventtrigger_0029()
        test_event_AGGREGATE: ddl_command_start,CREATE AGGREGATE
        test_event_AGGREGATE: ddl_command_end,CREATE AGGREGATE
    2、触发执行函数f_eventtrigger_0029()
        test_event_AGGREGATE: ddl_command_start,ALTER AGGREGATE
        test_event_AGGREGATE: ddl_command_end,ALTER AGGREGATE
    3、触发执行函数f_eventtrigger_0029()
        test_event_AGGREGATE: ddl_command_start,DROP AGGREGATE
        test_event_AGGREGATE: sql_drop,DROP AGGREGATE
        test_event_AGGREGATE: ddl_command_end,DROP AGGREGATE
History     : 
"""

import sys
import os
import unittest
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH


class IndexFileDamaged(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.logger.info('----------------this is setup-----------------------')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.DB_INSTANCE_PATH = macro.DB_INSTANCE_PATH
        self.comsh = CommonSH('PrimaryDbBmode')
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists start_eventtri;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists end_eventtri;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists drop_eventtri;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists rewrite_eventtri;")
        self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0029();")
        self.comsh.execut_db_sql('DROP function if exists f__eventtrigger_0029_01;')
        self.comsh.execut_db_sql('DROP function if exists f__eventtrigger_0029_02;')
        self.comsh.execut_db_sql('DROP type if exists type_eventtrigger_0029;')
        self.comsh.execut_db_sql("create type type_eventtrigger_0029 as (total bigint, count bigint);")
        msg = self.comsh.execut_db_sql(
            f"create or replace function f_eventtrigger_0029_01(state type_eventtrigger_0029, n int) return type_eventtrigger_0029 as declare new_state type_eventtrigger_0029;" \
            f" begin raise notice 'f_eventtrigger_0029_01 called with %', n;" \
            f" if state is null then if n is not null then new_state.total := n; new_state.count := 1; return new_state;" \
            f" end if; return null;" \
            f" elsif n is not null then state.total := state.total + n; state.count := state.count + 1; return state;" \
            f" end if;return null;end;")
        self.logger.info(msg)
        self.assertEqual("CREATE FUNCTION", msg)
        msg = self.comsh.execut_db_sql(
            f"create or replace function f_eventtrigger_0029_02(state type_eventtrigger_0029) return int4 as begin if state is null then" \
            f" return NULL;else return state.total / state.count; end if;end;")
        self.logger.info(msg)
        self.assertEqual("CREATE FUNCTION", msg)
        msg = self.comsh.execut_db_sql(
            f"CREATE OR REPLACE FUNCTION f_eventtrigger_0029() RETURNS event_trigger LANGUAGE plpgsql" \
            f" AS \$\$ BEGIN" \
            f" RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;" \
            f"END;" \
            f"\$\$;")
        self.logger.info(msg)
        self.assertEqual("CREATE FUNCTION", msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER start_eventtri ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0029();")
        self.logger.info(msg)
        self.assertEqual("CREATE EVENT TRIGGER", msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER end_eventtri ON ddl_command_end EXECUTE PROCEDURE f_eventtrigger_0029();")
        self.logger.info(msg)
        self.assertEqual("CREATE EVENT TRIGGER", msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER drop_eventtri ON sql_drop EXECUTE PROCEDURE f_eventtrigger_0029();")
        self.logger.info(msg)
        self.assertEqual("CREATE EVENT TRIGGER", msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER rewrite_eventtri ON table_rewrite EXECUTE PROCEDURE f_eventtrigger_0029();")
        self.logger.info(msg)
        self.assertEqual("CREATE EVENT TRIGGER", msg)

    def test_Index_file_damaged(self):
        self.logger.info(
            "创建AGGREGATE:create aggregate a_eventtrigger_0029(int4) (stype = type_eventtrigger_0029,sfunc = f_eventtrigger_0029_01,finalfunc = f_eventtrigger_0029_02);")
        msg = self.comsh.execut_db_sql(
            "create aggregate a_eventtrigger_0029(int4) (stype = type_eventtrigger_0029,sfunc = f_eventtrigger_0029_01,finalfunc = f_eventtrigger_0029_02);")
        self.logger.info(msg)
        self.assertIn(f'CREATE AGGREGATE', msg)
        self.assertIn(f'test_event_trigger: ddl_command_start CREATE AGGREGATE', msg)
        self.assertIn(f'test_event_trigger: ddl_command_end CREATE AGGREGATE', msg)

        self.logger.info(
            "AGGREGATE重命名:ALTER AGGREGATE a_eventtrigger_0029(int4) RENAME TO a_eventtrigger_00292;")
        msg = self.comsh.execut_db_sql(
            "ALTER AGGREGATE a_eventtrigger_0029(int4) RENAME TO a_eventtrigger_00292;")
        self.logger.info(msg)
        self.assertIn(f'test_event_trigger: ddl_command_start ALTER AGGREGATE', msg)
        self.assertIn(f'test_event_trigger: ddl_command_end ALTER AGGREGATE', msg)
        self.assertIn("ALTER AGGREGATE", msg)

        self.logger.info('删除AGGREGATE:drop aggregate a_eventtrigger_00292(int4);')
        msg = self.comsh.execut_db_sql('drop aggregate a_eventtrigger_00292(int4);')
        self.logger.info(msg)
        self.assertIn(f'test_event_trigger: ddl_command_start DROP AGGREGATE', msg)
        self.assertIn(f'test_event_trigger: sql_drop DROP AGGREGATE', msg)
        self.assertIn(f'test_event_trigger: ddl_command_end DROP AGGREGATE', msg)
        self.assertIn("DROP AGGREGATE", msg)

    def tearDown(self):
        self.logger.info('----------------this is tearDown-----------------------')
        # 删除事件触发器
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists start_eventtri;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists end_eventtri;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists drop_eventtri;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists rewrite_eventtri;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        # 删除事件触发器函数
        msg = self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0029();")
        self.logger.info(msg)
        self.assertEqual('DROP FUNCTION', msg)
        # 删除函数
        msg = self.comsh.execut_db_sql('DROP function if exists f_eventtrigger_0029_01;')
        self.logger.info(msg)
        self.assertEqual('DROP FUNCTION', msg)
        # 删除函数
        msg = self.comsh.execut_db_sql('DROP function if exists f_eventtrigger_0029_02;')
        self.logger.info(msg)
        self.assertEqual('DROP FUNCTION', msg)
        # 删除type
        msg = self.comsh.execut_db_sql('DROP type if exists type_eventtrigger_0029;')
        self.logger.info(msg)
        self.assertEqual('DROP TYPE', msg)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
